package GetSummaryDB;

use 5.012002;
use strict;
use warnings;

use utility;

use File::Find;
use File::Basename;
use File::Spec;
use File::Path;
use Cwd;
use Switch;
use Statistics::Descriptive;

# use XML::Simple;

use XML::LibXML;

# use XML::LibXML::Simple();

use List::Util qw(first max maxstr min minstr reduce shuffle sum);

# This will affect sysopen() usage.
# Solved constant conflict used in that function.
use Fcntl;

#require Exporter;

#our @ISA = qw(Exporter);

# Items to export into callers namespace by default. Note: do not export
# names by default without a very good reason. Use EXPORT_OK instead.
# Do not simply export all your public functions/methods/constants.

# This allows declaration	use GetSummaryDB ':all';
# If you do not need this, moving things directly into @EXPORT or @EXPORT_OK
# will save memory.
#our %EXPORT_TAGS = ( 'all' => [ qw(

#) ] );

#our @EXPORT_OK = ( @{ $EXPORT_TAGS{'all'} } );

#our @EXPORT = qw(

#);
our $VERSION = '0.01';

# Global variable
my $op_textRef;    # for log message display.
my $invalidValue = "-99999.00";

# Some common used function.
my $util = new utility;

# Preloaded methods go here.
sub new {
	my $package = shift;
	return bless( {}, $package );
}

sub GetSummaryDB {
	my $self = shift;

	# Query conditions
	my $filterRef           = shift;
	my $gen_dis_graphicsRef = shift;    # Generate the distribution or not?
	                                    # DB server name
	my $DBSourceNameRef     = shift;

	# Working directory
	my $workingdirRef = shift;

	# Output file, HTML file path.
	my $outputfileRef = shift;

	# Perl/Tk text widget reference
	$op_textRef = shift;

	if ($filterRef->{"stnType"} 	=~ /%/ ||
		$filterRef->{"stnSubType"} 	=~ /%/ ) {
		$util->logmsg( $op_textRef, "The station type and subtype must be chose" );
		return undef;
	}
	my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	  $util->get_DB_connectionInfo( $DBSourceNameRef, \"main" );

	$util->logmsg( $op_textRef,
		"DB Infor: \$DBServerName=$DBServerName, \$DBname=$DBname" );

	unless ( defined $DBServerName
		&& defined $DBname
		&& defined $DBusername
		&& defined $DBpassword )
	{
		$util->logmsg( $op_textRef, "Failed to get DB related information" );
		return undef;
	}

	# Get DB handle
	my $dbh = $util->connect_to_db_server_mssql(
		$DBServerName,
		$DBname,
		$DBusername, $DBpassword
	);

	# Get 1st test results
	my $sqlScript = $util->get_SQLScriptForNthTestResult(
		$filterRef,
		1,    # Nth test result.
		$util->get_TableName("General_data")
	);

	# $util->logmsg( $op_textRef, "1st run script:" . $sqlScript );
	my $res_1st_AoH = $util->run_query_hash( $dbh, $sqlScript );

	# Get 2nd test results
	$sqlScript = $util->get_SQLScriptForNthTestResult(
		$filterRef,
		2,    				# Nth test result.
		$util->get_TableName("General_data")
	);

	# $util->logmsg( $op_textRef, "2ndt run script:" . $sqlScript );
	my $res_2nd_AoH = $util->run_query_hash( $dbh, $sqlScript );

	# Get All test results
	$sqlScript =
	  $util->get_SQLScriptForAllTestResult( $filterRef,
		$util->get_TableName("General_data") );

	$util->logmsg( $op_textRef, "getting all records in main table" );
	my $res_all_AoH = $util->run_query_hash( $dbh, $sqlScript );
	$util->disconnDB($dbh);

	# Generate summary report
	## - Build summary table
	my @buildsummaryAoA;

	my (
		$summaryTableAoHoARef, $preto_1stRef, $preto_2nd_Ref,
		$errorcodeDespRef,     $errorcodeSourceRef
	) = gen_summaryTable( $res_1st_AoH, $res_2nd_AoH, $res_all_AoH );

	### Extracts a AoA for GenHtmlFile().
	@buildsummaryAoA = map [ keys(%$_), @$_{ keys(%$_) } ],
	  @$summaryTableAoHoARef;

	## get error code breakdown
	my $BreakdownTableHoARef;
	my @errorcodebreakdownAoA;
	$BreakdownTableHoARef = gen_errorCodeBreakdownTable(    #$res_1st_AoH,
		$preto_1stRef,
		$preto_2nd_Ref,
		$errorcodeSourceRef,
		$errorcodeSourceRef
	);

	### extract a AoA for GenHtmlFile()
	@errorcodebreakdownAoA = map [ $_,
		$BreakdownTableHoARef->{$_}[0],                     # Error Code
		$BreakdownTableHoARef->{$_}[1],                     # 1st run qty

		$BreakdownTableHoARef->{$_}[2],                     # 1st run %
		$BreakdownTableHoARef->{$_}[3],                     # 2nd run qty
		$BreakdownTableHoARef->{$_}[4] ],                   # 2nd run %
	  sort { $BreakdownTableHoARef->{$b}[0] <=> $BreakdownTableHoARef->{$a}[0] }

	  keys %$BreakdownTableHoARef;
	my $totalQty = 0;
	my $totalPct = 0;
	foreach (@errorcodebreakdownAoA) {
		$totalQty += $_->[1];
		$totalPct += $_->[2];
		$_->[2] = ( sprintf "%.2f", $_->[2] * 100 ) . "%";
		$_->[4] = ( sprintf "%.2f", $_->[4] * 100 ) . "%";
	}

	push @errorcodebreakdownAoA,
	  [ "Total Qty", $totalQty, int( $totalPct * 100 ) . "%" ];

	## Generate Pareto and test time boxplot.
	my ( $filename, $outputPath ) = fileparse($$outputfileRef);
	my $testtimeDatafile;
	my $testtimePNGFile;
	my $paretoDataFile;
	my $paretoPNGFilePath;
	gen_SummaryCharts(
		$res_1st_AoH,
		\$outputPath,

		# Error Code pareto
		\$paretoDataFile,
		\$paretoPNGFilePath,

		# Test time box plot
		\$testtimeDatafile,
		\$testtimePNGFile
	);

	my @summaryCharts;
	push @summaryCharts, $paretoPNGFilePath;
	push @summaryCharts, $testtimePNGFile;

	# Generate NV plotting charts
	my @runIDList = map { $_->{"G_Test_RunID"} }
	  grep { $_->{"G_Test_Result"} == 1 } @$res_1st_AoH;

	my $nvPlotAoA;
	my $processcapAoA;
	if ($$gen_dis_graphicsRef =~ /Yes/i) {
		if (   $filterRef->{"stnType"} =~ /XCOMBO/i
			|| $filterRef->{"stnType"} =~ /%/ )
		{
			$nvPlotAoA =
			  gen_NV_PlottingCharts( $DBSourceNameRef, \@runIDList,
				\$filterRef->{"prodID"},
				$workingdirRef, $outputfileRef );
		}

		# Generate distribution charts
		$processcapAoA = gen_processCapabilityCharts(
			$DBSourceNameRef, \@runIDList, $workingdirRef,
			$outputfileRef,   $filterRef
		);
		return undef unless defined($processcapAoA);
	}
	     # Generate html report
	$util->GenHtmlFile( $workingdirRef, \@buildsummaryAoA,
		\@errorcodebreakdownAoA, \@summaryCharts, $nvPlotAoA, $processcapAoA,
		$outputfileRef );
}

sub gen_processCapabilityCharts() {
	my $DBSourceNameRef = shift;
	my $runID_ListRef   = shift;

	my $workingdirRef = shift;
	my $outputfileRef = shift;
	my $filterRef     = shift;

	my $ProcessCapChartDataSetAoA;
	my @itemlizedDataForHtmlAoARef;

	# Check output path
	my ( $filename, $outputPath ) = fileparse($$outputfileRef);
	chdir($outputPath);
	my $processCapDataDir = "ProcessCap_Plot";
	my $processCapPlotDir = $outputPath . $processCapDataDir;
	if ( -d "$processCapPlotDir" ) {
		rmtree($processCapPlotDir);
	}
	mkpath("$processCapPlotDir");

	# Get data from tables
	my ( $dataFileAoARef, $dataFileForHtmlAoHRef ) = get_measurement(
		$DBSourceNameRef,    # DB source facory or lab
		                     # $DBtype, 				# main or reporting DB
		$runID_ListRef,      # run ID list
		$filterRef,          # where filters
		$outputPath,
		$processCapDataDir
	);

	push @$ProcessCapChartDataSetAoA, @$dataFileAoARef;
	push @itemlizedDataForHtmlAoARef, @$dataFileForHtmlAoHRef;

	# genreate charts
	my $ret =
	  $util->GenRChart_ProcessCapability( $op_textRef, \$outputPath,
		$ProcessCapChartDataSetAoA );

	if ( defined $ret ) {
		$util->logmsg( $op_textRef,
			"Failed to create Process capability chart" );
	}
	else {
		$util->logmsg( $op_textRef, "Process capability chart was created." );
	}

	return \@itemlizedDataForHtmlAoARef;
}

sub get_measurement {
	my $DBSourceNameRef   = shift;
	my $runID_ListRef     = shift;
	my $filterRef         = shift;
	my $outputPath        = shift;
	my $processCapDataDir = shift;

	my @dataSetAoA;
	my @itemlizedDataForHtmlAoAref;

	my $tmp_01_AoARef;
	my $tmp_02_AoARef;
	my @tmp;

	my $tableInfoHRef =
	  $util->get_tablesBySTNType( $DBSourceNameRef, \$filterRef->{"stnType"} );

	unless ( defined $tableInfoHRef ) {
		$util->logmsg( $op_textRef, "Failed to get table list and table info" );
		return undef;
	}

	my $id = 0;

	foreach my $table ( keys %$tableInfoHRef ) {
		$util->logmsg( $op_textRef, "get data from table: $table" );
		my $tabletype = $tableInfoHRef->{$table}{"tableType"};

		switch ($tabletype) {
			case "flat" {
				( $tmp_01_AoARef, $tmp_02_AoARef ) =
				  get_measurementFromFlatTable( $tableInfoHRef, \$id, \$table,
					$runID_ListRef, $filterRef, $outputPath,
					$processCapDataDir );

				if ( defined $tmp_01_AoARef && defined $tmp_02_AoARef ) {
					@dataSetAoA = ( @dataSetAoA, @$tmp_01_AoARef );
					@itemlizedDataForHtmlAoAref =
					  ( @itemlizedDataForHtmlAoAref, @$tmp_02_AoARef );
				}
			}
			case "nonFlat" {

				# ( $tmp_01_AoARef, $tmp_02_AoARef ) =
				# get_dataFromNonFlatTable( \$id, $DBSourceNameRef, "report",
				# $runID_ListRef, $filterRef, $outputPath, $processCapDataDir );

				( $tmp_01_AoARef, $tmp_02_AoARef ) =
				  get_measurementFromNonFlatTable( $tableInfoHRef, \$id,
					\$table, $runID_ListRef, $filterRef, $outputPath,
					$processCapDataDir );

				if ( defined $tmp_01_AoARef && defined $tmp_02_AoARef ) {
					@dataSetAoA = ( @dataSetAoA, @$tmp_01_AoARef );
					@itemlizedDataForHtmlAoAref =
					  ( @itemlizedDataForHtmlAoAref, @$tmp_02_AoARef );
				}
			}
			else {
				$util->logmsg( $op_textRef, "unknow table type:$tabletype  " );
				return undef;
			}

		}

	}

	( $tmp_01_AoARef, $tmp_02_AoARef ) =
	  get_dataFromNonFlatTable( \$id, $DBSourceNameRef, "report",
		$runID_ListRef, $filterRef, $outputPath, $processCapDataDir );

	if ( defined $tmp_01_AoARef && defined $tmp_02_AoARef ) {
		@dataSetAoA = ( @dataSetAoA, @$tmp_01_AoARef );
		@itemlizedDataForHtmlAoAref =
		  ( @itemlizedDataForHtmlAoAref, @$tmp_02_AoARef );
	}
	if ( scalar @dataSetAoA || scalar @itemlizedDataForHtmlAoAref ) {
		$util->logmsg( $op_textRef, "Can't get data from DB" );
	}
	return \@dataSetAoA, \@itemlizedDataForHtmlAoAref;

}

sub get_dataFromTable {
	my $DBSourceNameRef   = shift;
	my $runID_ListRef     = shift;
	my $filterRef         = shift;
	my $outputPath        = shift;
	my $processCapDataDir = shift;

	my @dataSetAoA;
	my @itemlizedDataForHtmlAoAref;

	my $tmp_01_AoARef;
	my $tmp_02_AoARef;
	my @tmp;

	# Get data from flat table
	my $id = 0;
	foreach my $DBtype ( "main", "report" ) {
		( $tmp_01_AoARef, $tmp_02_AoARef ) =
		  get_dataFromFlatTable( \$id, $DBSourceNameRef, $DBtype,
			$runID_ListRef, $filterRef, $outputPath, $processCapDataDir );

		if ( defined $tmp_01_AoARef && defined $tmp_02_AoARef ) {
			@dataSetAoA = ( @dataSetAoA, @$tmp_01_AoARef );
			@itemlizedDataForHtmlAoAref =
			  ( @itemlizedDataForHtmlAoAref, @$tmp_02_AoARef );
		}
	}

	( $tmp_01_AoARef, $tmp_02_AoARef ) =
	  get_dataFromNonFlatTable( \$id, $DBSourceNameRef, "report",
		$runID_ListRef, $filterRef, $outputPath, $processCapDataDir );

	if ( defined $tmp_01_AoARef && defined $tmp_02_AoARef ) {
		@dataSetAoA = ( @dataSetAoA, @$tmp_01_AoARef );
		@itemlizedDataForHtmlAoAref =
		  ( @itemlizedDataForHtmlAoAref, @$tmp_02_AoARef );
	}
	if ( scalar @dataSetAoA || scalar @itemlizedDataForHtmlAoAref ) {
		$util->logmsg( $op_textRef, "Can't get data from DB" );
	}
	return \@dataSetAoA, \@itemlizedDataForHtmlAoAref;

}

sub get_dataFromFlatTable {
	my $idRef             = shift;
	my $DBSourceNameRef   = shift;
	my $tableType         = shift;
	my $runID_ListRef     = shift;
	my $filterRef         = shift;
	my $outputPath        = shift;
	my $processCapDataDir = shift;

	my $tablesRef = $util->get_tableList($tableType);
	my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	  $util->get_DB_connectionInfo( $DBSourceNameRef, \$tableType );
	my $dbh =
	  $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername,
		$DBpassword );

	# my $id = 1;
	# my ( $dataSetAoARef, $itemlizedDataForHtmlAoAref );
	my ( @dataSetAoARef, @itemlizedDataForHtmlAoAref );
	for my $table (@$tablesRef) {
		$util->logmsg( $op_textRef, "From Table: $table" );
		my $resultHoARef =
		  get_allMeasurementFromFlatTable( $dbh, \$table, $runID_ListRef,
			$filterRef );
		next unless %$resultHoARef;

		#$idRef ++;
		my ( $tmp_01_Aref, $tmp_02_Aref ) =
		  gen_FilesForProcessCapCharts( $idRef, \$table, $resultHoARef,
			\$outputPath, $processCapDataDir );

		# my @tmp = (@dataSetAoARef, @$tmp_01_Aref);
		@dataSetAoARef = ( @dataSetAoARef, @$tmp_01_Aref );

		# @tmp = (@itemlizedDataForHtmlAoAref, @$tmp_02_Aref);
		@itemlizedDataForHtmlAoAref =
		  ( @itemlizedDataForHtmlAoAref, @$tmp_02_Aref );
	}

	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB( $dbh, $tablesRef );

	return ( \@dataSetAoARef, \@itemlizedDataForHtmlAoAref );

}

sub get_measurementFromFlatTable {
	my $tableInfoHRef = shift;
	my $idRef         = shift;

	# my $DBSourceNameRef   = shift;
	my $tableRef      = shift;
	my $runID_ListRef = shift;
	my $filterRef     = shift;

	my $outputPath        = shift;
	my $processCapDataDir = shift;

	my $DBserver = $tableInfoHRef->{$$tableRef}{"DBServer"};
	my $DBname   = $tableInfoHRef->{$$tableRef}{"DBName"};
	my ( $DBusername, $DBpassword ) =
	  $util->get_DBServerLoginInfo( \$DBserver );

	my $dbh =
	  $util->connect_to_db_server_mssql( $DBserver, $DBname, $DBusername,
		$DBpassword );

	# my ( $dataSetAoARef, $itemlizedDataForHtmlAoAref );
	my ( @dataSetAoARef, @itemlizedDataForHtmlAoAref );

	# for my $table (@$tablesRef) {

	# $util->logmsg( $op_textRef, "From Table: $$tableRef" );
	
	my $resultHoARef =
	  get_allMeasurementFromFlatTable( $dbh, $tableRef, $runID_ListRef,
		$filterRef );
	return undef unless defined $resultHoARef;

	#$idRef ++;
	my ( $tmp_01_Aref, $tmp_02_Aref ) =
	  gen_FilesForProcessCapCharts( $idRef, $tableRef, $resultHoARef,
		\$outputPath, $processCapDataDir );

	# my @tmp = (@dataSetAoARef, @$tmp_01_Aref);
	@dataSetAoARef = ( @dataSetAoARef, @$tmp_01_Aref );

	# @tmp = (@itemlizedDataForHtmlAoAref, @$tmp_02_Aref);
	@itemlizedDataForHtmlAoAref =
	  ( @itemlizedDataForHtmlAoAref, @$tmp_02_Aref );

	# }

	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB($dbh);

	return ( \@dataSetAoARef, \@itemlizedDataForHtmlAoAref );

}

sub gen_FilesForProcessCapCharts {
	my $idRef             = shift;
	my $tableRef          = shift;
	my $resultHoARef      = shift;
	my $outputPathRef     = shift;
	my $processCapDataDir = shift;

	my @dataSetAoA;
	my @itemlizedDataForHtmlAoAref;

	for my $testcaseName ( keys %{$resultHoARef} ) {

		# must have more than one results
		# next unless scalar @{ $resultHoARef->{$testcaseName} } > 0;

		# the unique measurement result must be more than 3
		# to calculate statistics indices.
		my %tmp = map { $_ => 1 } @{ $resultHoARef->{$testcaseName} };
		my $sizeOfMeasurement = scalar keys %tmp;

		my $minQty = 3;
		if ( $sizeOfMeasurement < $minQty ) {
			$util->logmsg( $op_textRef,
				"unique measurement less than $minQty" );
			next;
		}

		$$idRef++;
		my $datasourceFile =
		  "$$idRef-" . $$tableRef . "-" . $testcaseName . "-datasource.txt";
		my $pngFile = "$$idRef-" . $$tableRef . "-" . $testcaseName . ".png";
		my $limitFile =
		  "$$idRef-" . $$tableRef . "-" . $testcaseName . "-limits.txt";

		my $filenameData =
		  $$outputPathRef . "$processCapDataDir/" . $datasourceFile;

		my $filenamePNG = $$outputPathRef . "$processCapDataDir/" . $pngFile;
		my $filenameLit = $$outputPathRef . "$processCapDataDir/" . $limitFile;

		my $samplesize = keys %{$resultHoARef};

	  # my $tmpFileName = File::Spec->catfile( $$outputPathRef, $filenameData );
		$util->logmsg( $op_textRef, "$filenameData" );

		my @IndividualDataSetA;

		# push the Column ID
		push( @IndividualDataSetA, $idRef );

		my ( $lsl, $target, $usl, $unit ) =
		  get_limitsForCurrentTest( \@{ $resultHoARef->{$testcaseName} } );

		sysopen( FH, $filenameLit, O_WRONLY | O_TRUNC | O_CREAT )
		  || die $!;
		print FH "lsl \t t \t usl \t unit \n";
		print FH "$lsl \t $target \t $usl \t $unit \n";
		close(FH);

		# push the Limit file path
		push( @IndividualDataSetA, $filenameLit );

		# write test result to test data file
		sysopen( FH, $filenameData, O_WRONLY | O_TRUNC | O_CREAT )
		  || die $!;
		print FH "$testcaseName \n";

		# print "$testcaseName \n";
		foreach my $measurement ( @{ $resultHoARef->{$testcaseName} } ) {

			# $_ =~ s/^\s+|\s+$//g;
			# $_ =
			#  int( $_ * ( 10**$$DecimalLenRef ) ) / ( 10**$$DecimalLenRef );
			# get rid of some 'undef' and '-99999.00' results
			# next if undef $measurement;
			next if $measurement == '';
			next if ( $measurement =~ /-99999.00/ );

			print FH "$measurement\n";

			# print "$measurement\n";

		}
		close(FH);

		push( @IndividualDataSetA, $filenameData );
		push( @IndividualDataSetA, $filenamePNG );

		# push the current column data to AoA
		push( @dataSetAoA, \@IndividualDataSetA );

		# build arrays for web page.
		push @itemlizedDataForHtmlAoAref,
		  [
			$$idRef, $lsl, $usl, $unit, $testcaseName,
			"./$processCapDataDir/" . $pngFile
		  ];

		# next test title field
		# $$idRef++;
	}

	return ( \@dataSetAoA, \@itemlizedDataForHtmlAoAref );

}

sub get_measurementFromNonFlatTable {
	my $tableInfoHRef = shift;
	my $idRef         = shift;

	# my $DBSourceNameRef   = shift;
	# my $tableType         = shift;
	my $tableRef          = shift;
	my $runID_ListRef     = shift;
	my $filterRef         = shift;
	my $outputPath        = shift;
	my $processCapDataDir = shift;

	# my $tableListOnReportDB = $util->get_NonFlattableList( \$tableType );

	my $DBserver = $tableInfoHRef->{$$tableRef}{"DBServer"};
	my $DBname   = $tableInfoHRef->{$$tableRef}{"DBName"};
	my ( $DBusername, $DBpassword ) =
	  $util->get_DBServerLoginInfo( \$DBserver );

	# my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	#   $util->get_DB_connectionInfo( $DBSourceNameRef, \$tableType );
	# my $dbh =
	#   $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername,
	#	$DBpassword );
	my $dbh =
	  $util->connect_to_db_server_mssql( $DBserver, $DBname, $DBusername,
		$DBpassword );

	my @dataSetAoA;
	my @itemlizedDataForHtmlAoAref;

	my $resultHoARef =
	  get_allMeasurementFromNonFlatTable( $dbh, $tableRef, $runID_ListRef,
		$filterRef );

	# return undef if scalar keys %$resultHoARef == 0;
	next if scalar keys %$resultHoARef == 0;

	my ( $tmp_01, $tmp_02 ) =
	  gen_FilesForProcessCapCharts( $idRef, $tableRef, $resultHoARef,
		\$outputPath, $processCapDataDir );

	@dataSetAoA = ( @dataSetAoA, @$tmp_01 );
	@itemlizedDataForHtmlAoAref = ( @itemlizedDataForHtmlAoAref, @$tmp_02 );

	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB($dbh);

	return ( \@dataSetAoA, \@itemlizedDataForHtmlAoAref );

}

sub get_dataFromNonFlatTable {
	my $idRef             = shift;
	my $DBSourceNameRef   = shift;
	my $tableType         = shift;
	my $runID_ListRef     = shift;
	my $filterRef         = shift;
	my $outputPath        = shift;
	my $processCapDataDir = shift;

	my $tableListOnReportDB = $util->get_NonFlattableList( \$tableType );

	# my $tablesRef = $util->get_tableList($tableType);
	my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	  $util->get_DB_connectionInfo( $DBSourceNameRef, \$tableType );
	my $dbh =
	  $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername,
		$DBpassword );

	my @dataSetAoA;
	my @itemlizedDataForHtmlAoAref;

	for my $table (@$tableListOnReportDB) {

		my $resultHoARef =
		  get_allMeasurementFromNonFlatTable( $dbh, \$table, $runID_ListRef,
			$filterRef );

		# return undef if scalar keys %$resultHoARef == 0;
		next if scalar keys %$resultHoARef == 0;

		my ( $tmp_01, $tmp_02 ) =
		  gen_FilesForProcessCapCharts( $idRef, \$table, $resultHoARef,
			\$outputPath, $processCapDataDir );

		@dataSetAoA = ( @dataSetAoA, @$tmp_01 );
		@itemlizedDataForHtmlAoAref = ( @itemlizedDataForHtmlAoAref, @$tmp_02 );
	}

	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB( $dbh, " $DBServerName - $DBname" );

	return ( \@dataSetAoA, \@itemlizedDataForHtmlAoAref );

}

sub get_limitsForCurrentTest() {

	my $currentcolumnRef = shift;

	my $stat = Statistics::Descriptive::Full->new();

	# Get rid of some extrem outliers.
	$util->logmsg(
		$op_textRef,
		"\@\$currentcolumnRef before percentile calculation: "
		  . @$currentcolumnRef,
		"\n"
	);

	$stat->add_data( \@$currentcolumnRef );
	my $Q1   = $stat->percentile(25);
	my $Q3   = $stat->percentile(75);
	my $mean = $stat->mean();
	
	unless (defined $Q1 && defined $Q3 && defined $mean ) {
		$Q1 = $stat->min();
		$Q3 = $stat->max();
		$mean = $stat->mean();
	}
	
	my @emptyarray;
	$stat->add_data( \@emptyarray );

	my $IQR = $Q3 - $Q1;
	print "\$Q1=$Q1, \$Q3=$Q3, Q3-Q1= $IQR \n";
	print "extrem outlier border=", ( $Q1 - 3 * $IQR ), " , ",
	  ( $Q3 + 3 * $IQR ), "\n";
	my $lsl = $Q1 - 3 * $IQR;
	my $usl = $Q3 + 3 * $IQR;
	$util->logmsg( $op_textRef, "\$lsl = $lsl, \$usl = $usl" );

	return ( $lsl, $mean, $usl, "dBm" );

}

sub get_allMeasurementFromFlatTable() {

	# my $self 		= shift;
	my $dbh       = shift;
	my $tableRef  = shift;
	my $runIDARef = shift;
	my $filter    = shift;

	# get column type
	my $script = $util->get_SQLScriptDataType($tableRef);
	my $columnDataTypeAoHRef = $util->run_query_hash( $dbh, $$script );
	my @columnlist = map { $_->{"COLUMN_NAME"} } @$columnDataTypeAoHRef;

	my $runIDColumnName = $util->getRunIDColumnName($tableRef);
	if ( defined $runIDColumnName ) {
		$util->logmsg( $op_textRef, "runID Column: $runIDColumnName" );
	}
	else {
		$util->logmsg( $op_textRef,
			"Can't get runID column name for current table" );
		return undef;
	}

	my @measurementAoHRef;
	foreach my $runID (@$runIDARef) {
		print ".";
		my $script_tmp =
		  $util->get_SQLScriptforMeasOfOneRunID( $tableRef, \@columnlist,
			$runID, $runIDColumnName );

		# The query might return multi records due to the table structure.
		# Don't use run_query_selectrow_hashref()
		my $resultAoHRef = $util->run_query_hash( $dbh, $script_tmp );

		# check if only one record returned
		# multi records should not happen
		my $noOfRecords = scalar(@$resultAoHRef);
		if ( $noOfRecords == 0 ) {

			# $util->logmsg( $op_textRef, "No returnes current runID: $runID" );
			next;
		}
		elsif ( $noOfRecords > 1 ) {

			# $util->logmsg( $op_textRef,
			# 	"Returnes more than one record for current runID: $runID" );
			return undef;

		}

		# some record returns undef for all measurement. check it out.
		
		# map { $resultAoHRef->[0]{$_} => 1 } keys %{ $resultAoHRef->[0] };
		# don't use map, caused memory leak due to some undefined value
		my %tmp;
		while ( my ($k, $v) = each %{$resultAoHRef->[0]}) {
			next unless defined $v;
			$tmp{$v} = 1;
		}
		my $sizeOfhashValue = scalar keys %tmp;
		if ( $sizeOfhashValue == 0 ) {
			# $util->logmsg( $op_textRef, "No measurement for current run ID;" );
			next;
		}

		push @measurementAoHRef, $resultAoHRef->[0];
	}
	if ( scalar @measurementAoHRef == 0 ) {
		$util->logmsg( $op_textRef, "No measurement was found" );
		return undef;
	}
	my %resultHoA;
	foreach my $recHRef (@measurementAoHRef) {

		# $util->logmsg( $op_textRef, "current measurement:" );
		while ( my ( $key, $value ) = each(%$recHRef) ) {
			next unless ( defined $value );
			next if ( $value =~ /$invalidValue/ );

			# $util->logmsg( $op_textRef, $value );
			push @{ $resultHoA{$key} }, $value;
		}

	}

	return \%resultHoA;
}

sub get_allMeasurementFromNonFlatTable() {

	# my $self 		= shift;
	my $dbh       = shift;
	my $tableRef  = shift;
	my $runIDARef = shift;
	my $filter    = shift;

	# get column type
	my $script = $util->get_SQLScriptDataType($tableRef);
	my $columnDataTypeAoHRef = $util->run_query_hash( $dbh, $$script );
	my @columnlist = map { $_->{"COLUMN_NAME"} } @$columnDataTypeAoHRef;

	my $bandColName      = "Band";
	my $rxPathColName    = "RxPath";
	my $channelIDColName = "ChannelID";
	push @columnlist, $bandColName;
	push @columnlist, $rxPathColName;
	push @columnlist, $channelIDColName;

	my $measurementAoHRef;
	foreach my $runID (@$runIDARef) {
		# print ".";
		$script =
		  $util->get_SQLScriptforMeasOfOneRunID( $tableRef, \@columnlist,
			$runID, "RunID" );
		my $resultAoHRef = $util->run_query_hash( $dbh, $script );

		my $noOfRecords = scalar(@$resultAoHRef);
		if ( $noOfRecords == 0 ) {
			$util->logmsg( $op_textRef, "No returnes for runID: $runID" );
			next;
		}

		# elsif ($noOfRecords > 1 ) {
		#	$util->logmsg( $op_textRef,
		#		"Returnes more than one records for runID: $runID" );
		#	return undef;
		# }

		my %currResultAoHRef;
		my $testcaseName;
		foreach my $currHRef (@$resultAoHRef) {

			for my $k ( keys %$currHRef ) {

				next
				  if ( $k eq $bandColName
					|| $k eq $rxPathColName
					|| $k eq $channelIDColName );

				$testcaseName =

				  #$$tableRef . $k
				  $k
				  . "_$currHRef->{$bandColName}"
				  . "_$currHRef->{$rxPathColName}"
				  . "_$currHRef->{$channelIDColName}";
				$currResultAoHRef{$testcaseName} = $currHRef->{$k};
			}
		}

		push @$measurementAoHRef, \%currResultAoHRef;
	}

	my %resultHoA;
	foreach my $recHRef (@$measurementAoHRef) {

		# $util->logmsg( $op_textRef, "current measurement:" );
		while ( my ( $key, $value ) = each(%$recHRef) ) {
			next unless ( defined $value && $value !~ /$invalidValue/ );

			# if ( $value =~ /$invalidValue/ ) {
			#	next;
			# }

			$util->logmsg( $op_textRef, $value );
			push @{ $resultHoA{$key} }, $value;
		}
	}

	return \%resultHoA;
}

sub gen_NV_PlottingCharts() {
	my $DBSourceNameRef = shift;
	my $runID_ListRef = shift;
	my $modelNoRef    = shift;
	my $workingdirRef = shift;
	my $outputfileRef = shift;

	# Get all NVs will be plotted and all their attributes
	my $nvAttrHoHRef = get_NVAttr( $workingdirRef, $modelNoRef );
	my ( $filename, $outputPath ) = fileparse($$outputfileRef);
	my $allNVvalueHoHRef = get_allNVvaluesFromTable( 	$nvAttrHoHRef, $runID_ListRef,
														$DBSourceNameRef );
	if ( scalar( keys %{$allNVvalueHoHRef} ) == 0 ) {
		$util->logmsg( $op_textRef, "No NV value returned from DB..." );
		return undef;
	}

	#
	chdir($outputPath);
	my $nvPlotFolder  = "NV_Plotting";
	my $nvPlottingDir = $outputPath . "/$nvPlotFolder";
	if ( -d "$nvPlottingDir" ) {
		rmtree($nvPlottingDir);
	}
	mkpath("$nvPlottingDir");

	# save the data to file.
	# {nv}, [0] --- data file, [1] --- PNG file, [2] --- plot title
	my %nvFileInfoHoA;
	my @nvPlotSummaryAoA;
	my @tempA;

	for my $nvID ( keys %{$allNVvalueHoHRef} ) {
		my $nvName   = $nvAttrHoHRef->{$nvID}->{"Name"};
		my $fileData = $nvID . "-" . $nvName . "datasource.txt";
		my $filePNG  = $nvID . "-" . $nvName . ".png";

		my $filenameData = "$outputPath" . "$nvPlotFolder/" . $fileData;
		my $filenamePNG  = "$outputPath" . "$nvPlotFolder/" . $filePNG;

		my $samplesize = scalar( @{ $allNVvalueHoHRef->{$nvID}{0} } );
		$nvFileInfoHoA{$nvID} = [
			$filenameData, $filenamePNG,
			$nvName . '[Samples: ' . $samplesize . ']'
		];

		$util->logmsg( $op_textRef, "$filenameData" );
		sysopen( FH, $filenameData, O_WRONLY | O_TRUNC | O_CREAT ) || die $!;
		print FH "group	\t	value  \n";

		# $util->logmsg( $op_textRef, "group	\t	value  \n");
		for my $currIdx ( keys %{ $allNVvalueHoHRef->{$nvID} } ) {
			foreach ( @{ $allNVvalueHoHRef->{$nvID}->{$currIdx} } ) {
				print FH "$currIdx \t $_  \n";
				# $util->logmsg( $op_textRef, "$currIdx \t $_");
			}
		}
		close(FH);

		push @tempA, [	$nvID,
						$nvAttrHoHRef->{$nvID}{"LowLimit"},
						$nvAttrHoHRef->{$nvID}{"HiLimit"},
						"NA", 
						undef, 
						"./$nvPlotFolder/" . $filePNG,
						$nvName ];								# save the nv name, used to sort the the order of charts later
	}

	# sort the charts by nv names.
	@nvPlotSummaryAoA = sort {$a->[6] cmp $b->[6]} @tempA;
	
	$util->GenRChart_Boxplot( \%nvFileInfoHoA, \$outputPath, \1 );
	print "";

	$util->logmsg( $op_textRef, "boxplot generated" );

	return \@nvPlotSummaryAoA;
}

sub get_allNVvaluesFromTable() {
	my $nvAttrHoHRef  = shift;
	my $runID_ListRef = shift;

	my $DBSourceNameRef = shift;

	my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	  $util->get_DB_connectionInfo( $DBSourceNameRef, \"report" );

	$util->logmsg( $op_textRef,
		"DB Infor: \$DBServerName=$DBServerName, \$DBname=$DBname" );
	unless ( defined $DBServerName
		&& defined $DBname
		&& defined $DBusername
		&& defined $DBpassword )
	{
		$util->logmsg( $op_textRef, "Failed to get DB related information" );
		return undef;
	}

	my $dbh =
	  $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername,
		$DBpassword );

	my %nvValues;
	$util->logmsg( $op_textRef, "Total runID:" . scalar(@$runID_ListRef) );
	$util->logmsg( $op_textRef, "get nv data from xml files:" );

	foreach my $runID (@$runID_ListRef) {
		$util->logmsg( $op_textRef, "." );

		my $sqlScript = $util->get_SQLScriptForCurrentRunID(
			"*",               # Column
			"Tbl_RFCal_NV",    # Table
			#  "Tbl_RFCal_NV_MC7700",
			$runID
		);

		# my $NVXMLFile = $util->run_query_selectrow_hashref( $dbh, $sqlScript );
		my $NVsOfCurrentRunIDAoHRef = $util->run_query_hash( $dbh, $sqlScript );

		if (scalar @$NVsOfCurrentRunIDAoHRef == 0) {
			$util->logmsg( $op_textRef, "the following queyr returns nothing ????????????????");
			$util->logmsg( $op_textRef, "$sqlScript" );
			next;
		};


		# Loop through all NVs from NV XML file
		foreach my $currH (@$NVsOfCurrentRunIDAoHRef) {
			my $nvID    = $currH->{"NV_ID"};
			my $nvIdx   = $currH->{"NVe_Idx"};
			my $nvValue = $currH->{"NVe_Value"};

			next unless defined $nvAttrHoHRef->{$nvID};
			push @{ $nvValues{$nvID}{$nvIdx} }, $nvValue;
		}
	}
	print "\n";

	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB($dbh);

	return \%nvValues;
}

sub get_allNVvalues() {
	my $nvAttrHoHRef  = shift;
	my $runID_ListRef = shift;

	# my $DBServerRef   = shift;
	my $DBSourceNameRef = shift;

	# my $DBusernameRef = shift;
	# my $DBpasswordRef = shift;

	# my ($DBServerName, $DBname, $DBusername, $DBpassword)	=
	#			$util->get_DB_connectionInfo ($DBSourceNameRef, \"DB_main");

	my ( $DBServerName, $DBname, $DBusername, $DBpassword ) =
	  $util->get_DB_connectionInfo( $DBSourceNameRef, \"report" );

	$util->logmsg( $op_textRef,
		"DB Infor: \$DBServerName=$DBServerName, \$DBname=$DBname" );
	unless ( defined $DBServerName
		&& defined $DBname
		&& defined $DBusername
		&& defined $DBpassword )
	{
		$util->logmsg( $op_textRef, "Failed to get DB related information" );
		return undef;
	}

	# my $dbh =
	#  $util->connect_to_db_server_mssql( $$DBServerRef,
	#	$util->get_DBName( $DBServerRef, "DB_main" ),
	#	$$DBusernameRef, $$DBpasswordRef );

	my $dbh =
	  $util->connect_to_db_server_mssql( $DBServerName, $DBname, $DBusername,
		$DBpassword );

	# create object
	# my $xml = new XML::Simple; # Don't use XML::LibXML, it's too slow
	my $parser = XML::LibXML->new();

	my %nvValues;
	$util->logmsg( $op_textRef, "Total runID:" . scalar(@$runID_ListRef) );
	$util->logmsg( $op_textRef, "get nv data from xml files ..." );

	foreach my $runID (@$runID_ListRef) {

		print ".";
		my $sqlScript = $util->get_SQLScriptForRunID(
			"G_Test_NVFile_XML",     # Column
			"dbo.TBL_RFCAL_DATA",    # Table
			$runID
		);
		my $NVXMLFile = $util->run_query_selectrow_hashref( $dbh, $sqlScript );
		next unless defined $NVXMLFile->{"G_Test_NVFile_XML"};

		my $nvFileXML =
		  $parser->parse_string( $NVXMLFile->{"G_Test_NVFile_XML"} );

		# Loop through all NVs from NV XML file

		foreach my $currNvNode ( $nvFileXML->findnodes('/NVITEMS/NV') ) {
			my $nvID = $currNvNode->getAttribute("ID");
			next unless defined( $nvAttrHoHRef->{$nvID} );

			#my $buf = sprintf "nvID: %s nvName: %s Length: %s",
			#						$nvID,
			#						$nvAttrHoHRef->{$nvID}{"Name"},
			#						$nvAttrHoHRef->{$nvID}->{"SizeOf"};
			#$util->logmsg( $op_textRef, $buf);

			my @nvIndexes = $currNvNode->getElementsByTagName('*');
			my $idx       = 0;
			foreach my $currIndex (@nvIndexes) {
				my $nv = $currIndex->to_literal();
				push @{ $nvValues{$nvID}{$idx} }, $nv;
				$idx++;
			}
		}
	}
	$util->logmsg( $op_textRef, "disconnect DB" );
	$util->disconnDB($dbh);

	return \%nvValues;
}

sub get_NVAttr() {
	my $workingdirRef = shift;
	my $modelNoRef    = shift;

	my $parser = XML::LibXML->new();

	$util->logmsg( $op_textRef, "Getting all NV attribute" );

	# Get image.gif
	my $xmlfile     = $$workingdirRef . "/NV_File/image.gif";
	my $image       = $parser->parse_file($xmlfile);
	my @imageNVList = $image->findnodes('/NvDefinition/NvItem');

	# Get NV limit file
	my $nvAttrHoH = get_limits( $workingdirRef, $modelNoRef );

	while ( my ( $key, $value ) = each( %{$nvAttrHoH} ) ) {
		print ".";

		# Get attribute for NVs in NV Limit file
		foreach my $NvItem (@imageNVList) {
			my $id = $NvItem->getAttribute("id");
			next unless $id eq $key;

			my $name = $NvItem->getAttribute("name");

			my @member = $NvItem->findnodes("Member");
			my $type   = $member[0]->getAttribute("type");
			my $sizeOf = $member[0]->getAttribute("sizeOf");

			$nvAttrHoH->{$id}{"Name"}   = $name;
			$nvAttrHoH->{$id}{"Type"}   = $type;
			$nvAttrHoH->{$id}{"SizeOf"} = $sizeOf;
		}
	}
	print "\n";
	return $nvAttrHoH;
}

sub get_limits() {
	my $workingdirRef = shift;
	my $modelNoRef    = shift;
	my $file = $$workingdirRef . "/NV_File/$$modelNoRef" . "_Limits.txt";

	open FILE, "<", "$file" or die $!;
	my @f = <FILE>;
	close FILE;

	my @limits = grep ( /^\d/, @f );

	# print @limits;

	my %limitsHoH;
	for (@limits) {
		my @tmp = split(/,/);
		$limitsHoH{ $tmp[0] } = {
			"LowLimit" => $tmp[1],
			"HiLimit"  => $tmp[2]
		};
	}
	return \%limitsHoH;
}

sub printRecords() {
	my $Records_AoH = shift;

	my @tmp = keys %{ $Records_AoH->[0] };
	$util->logmsg( $op_textRef, join "%", @tmp );

	my $titlePrinted = 0;

	for my $href (@$Records_AoH) {
		my @theValues;
		for my $currKey (@tmp) {
			push @theValues, $href->{$currKey};
		}
		$util->logmsg( $op_textRef, join "%", @theValues );
	}

}

sub gen_errorCodeBreakdownTable() {
	my $pareto_1stHRef     = shift;
	my $pareto_2ndHRef     = shift;
	my $errorcodeDespRef   = shift;
	my $errorcodeSourceRef = shift;

	my $breakdownTableHoARef;

	my $totalQty;
	for my $value ( values %$pareto_1stHRef ) {
		$totalQty += $value;
	}

	# Make up the return table
	%$breakdownTableHoARef = map {
		$_ =>    # Error Code
		  [
			$pareto_1stHRef->{$_},    # Error Code Qty
			$pareto_1stHRef->{$_} / $totalQty,    # 1st %
			( defined $pareto_2ndHRef->{$_} ) ? $pareto_2ndHRef->{$_} : 0, # 2nd
			$pareto_2ndHRef->{$_} / $totalQty,    # 2nd %
			"$errorcodeDespRef->{$_} in $errorcodeSourceRef->{$_}"
		  ]
	  } sort { $pareto_1stHRef->{$b} <=> $pareto_1stHRef->{$a} }
	  keys %$pareto_1stHRef;

	return $breakdownTableHoARef;
}

sub gen_summaryTable() {
	my $result_1st_AoH = shift;
	my $result_2nd_AoH = shift;
	my $res_all_AoH    = shift;

	my $tableRef;    #       = shift;
	                 # Quantity
	my $totalQty = scalar(@$result_1st_AoH);
	push @$tableRef, { "Qty Tested" => [$totalQty] };

	# 1st pass yield
	my ( $yield_1st, $yield_2nd, $yield_overall, $pareto_Of_1stTestResultRef,
		$pareto_Of_2ndTestResultRef, $errorcodeDescRef, $errorcodeSourceRef )
	  = get_Yield( $result_1st_AoH, $result_2nd_AoH, $res_all_AoH );

	push @$tableRef,
	  { "First_Pass_Yield" => [ sprintf '%.2f%%', $yield_1st * 100 ] };

	# 1st+2nd Yield
	push @$tableRef,
	  { "1st+2nd_Yield" =>
		  [ sprintf '%.2f%%', ( $yield_1st + $yield_2nd ) * 100 ] };

	# Overall Yield
	push @$tableRef,
	  { "Overall Yield" => [ sprintf '%.2f%%', $yield_overall * 100 ] };

	# Start and End Date
	## Get date from 1st record
	my ( $date_start, $date_end ) = get_dateRange($result_1st_AoH);
	push @$tableRef, { "StartDate" => [$date_start] };
	push @$tableRef, { "EndDate"   => [$date_end] };

	# Test Code Rev
	my @testCodeListH =
	  $util->return_unique( map $_->{"G_App_Software_Version"},
		@$result_1st_AoH );
	push @$tableRef, { "TestCode Rev" => [@testCodeListH] };

	# BOM Rev
	my @BOMListH =
	  $util->return_unique( map $_->{"G_FSN_BOM"}, @$result_1st_AoH );
	push @$tableRef, { "BOM Rev" => [@BOMListH] };

	# DUT FW Rev
	my @FWListH =
	  $util->return_unique( map $_->{"G_Firmware_Version"}, @$result_1st_AoH );
	push @$tableRef, { "DUT FW Rev" => [@FWListH] };

	# STN used
	my @STNListH =
	  $util->return_unique( map $_->{"G_Computer_ID"}, @$result_1st_AoH );
	foreach (@STNListH) {
		s/\s//g;
	}
	push @$tableRef, { "STN List" => [@STNListH] };

	# DUT factory ID
	my @factoryIDListH =
	  $util->return_unique( map $_->{"G_FSN_FactoryCode"}, @$result_1st_AoH );
	push @$tableRef, { "Factory ID" => [@factoryIDListH] };

	# Product Model
	my @ModelListH =
	  $util->return_unique( map $_->{"G_DUT_Type"}, @$result_1st_AoH );
	push @$tableRef, { "Product Model" => [@ModelListH] };

	# Date report generated
	my $DataTime = scalar( localtime() );
	push @$tableRef, { "Report Generation Time" => [$DataTime] };

	return $tableRef, $pareto_Of_1stTestResultRef, $pareto_Of_2ndTestResultRef,
	  $errorcodeDescRef, $errorcodeSourceRef;
}

sub get_dateRange() {
	my $records_AoHRef = shift;
	my @tmp;
	@tmp =
	  sort { $a->{"DB_Primary_Key"} <=> $b->{"DB_Primary_Key"} }
	  @$records_AoHRef;

	my $start_date = $tmp[0]->{"DB_Date_Time_Inserted"};
	my $end_date   = $tmp[ scalar(@tmp) - 1 ]->{"DB_Date_Time_Inserted"};

	#@tmp = keys %{$records_AoHRef[0]};

	return $start_date, $end_date;

}

sub get_Yield() {
	my $res_1st_AoH_Ref = shift;
	my $res_2nd_AoH_Ref = shift;
	my $res_all_AoH_Ref = shift;

	my $yield_1st;
	my $yield_2nd;
	my $yield_all;

	my $totalQty = scalar(@$res_1st_AoH_Ref);
	$util->logmsg( $op_textRef, "Total from 1st run:" . $totalQty );

	# Get 1st error code by FSN, {"FSN" => "ErrorCode"}
	my %errorcode_1st =
	  map { $_->{"G_FSN"}, $_->{"G_Error_1_Code"} } @$res_1st_AoH_Ref;
	$util->logmsg( $op_textRef, "1st run FSNs:" . keys(%errorcode_1st) );
	my %paretoDataOf1stTest;
	while ( my ( $key, $value ) = each(%errorcode_1st) ) {

		# print "\%errorcode_1st: $key => $value\n";
		$paretoDataOf1stTest{$value}++;
	}
	$yield_1st = $paretoDataOf1stTest{"OK"} / $totalQty;

	# Get 2nd error code by FSN, {"FSN" => "ErrorCode"}
	# Find FSNs failed 1st time only.
	# Count FSN failed 1st time only.
	my %tmp =
	  map { $_->{"G_FSN"}, $_->{"G_Error_1_Code"} } @$res_2nd_AoH_Ref;
	$util->logmsg( $op_textRef, "2nd run FSNs:" . keys(%tmp) );

	my %errorcode_2nd;
	while ( my ( $key, $value ) = each(%errorcode_1st) ) {

		# Failure units from 1st only
		next if ( $value =~ /OK/ );

		# Units tested twice
		next unless ( defined $tmp{$key} );

		# print "\%errorcode_1st: $key => $value\n";
		$errorcode_2nd{$key} = $tmp{$key};
	}

	# Count qty by error code.
	my %paretoDataOf2ndTest;
	while ( my ( $key, $value ) = each(%errorcode_2nd) ) {

		# print "\%errorcode_2nd: $key => $value\n";
		$paretoDataOf2ndTest{$value}++;
	}
	$yield_2nd = $paretoDataOf2ndTest{"OK"} / $totalQty;

	# Get all OK units from @$res_all_AoH_Ref
	my %unitPasses =
	  map { $_->{"G_FSN"}, $_->{"G_Error_1_Code"} }
	  grep { $_->{"G_Error_1_Code"} =~ /OK/ } @$res_all_AoH_Ref;

	# Get pass record for all FSNs in  %errorcode_1st
	# can't use map here. some undef value will cause memory leak.
	# %tmp = map { $_, $unitPasses{$_} } keys(%errorcode_1st);
	
	# loop a hash	
	%tmp =();
	while (my ($sn, $errorcode) = each (%errorcode_1st)) {
		next unless defined $unitPasses{$sn};
		$tmp{$sn} = $unitPasses{$sn};
	}
	
	
	$yield_all = ( scalar grep { $_ =~ /OK/ } values %tmp ) / $totalQty;

	# Get errorcode description
	my %errorCodeAndDesc =
	  map { $_->{"G_Error_1_Code"}, $_->{"G_Error_1_Desc"} } @$res_1st_AoH_Ref;

	# Get error code source information
	my %errorCodeSouce =
	  map { $_->{"G_Error_1_Code"}, $_->{"G_Error_1_Source"} }
	  @$res_1st_AoH_Ref;

	return $yield_1st, $yield_2nd, $yield_all, \%paretoDataOf1stTest,
	  \%paretoDataOf2ndTest, \%errorCodeAndDesc, \%errorCodeSouce;
}

sub gen_SummaryCharts() {
	my $res_AoH         = shift;
	my $outputFolderRef = shift;

	# Pareto data file path
	my $paretoDataFileRef    = shift;
	my $paretoPNGFilePathRef = shift;

	my $testtimeDatafileRef = shift;
	my $testtimePNGFileRef  = shift;

	my $record_Ref_H;
	my %restultHoA;

	# Get error code list
	my %firstRunErrorCode;
	my @errorCodeList = map $_->{"G_Error_1_Code"}, @$res_AoH;
	foreach (@errorCodeList) {
		$firstRunErrorCode{$_}++;
	}
	$util->logmsg( $op_textRef, join( ",", @errorCodeList ) );

	# Generate error code pareto chart
	chdir($$outputFolderRef);
	my $sumChartsFolder = "images/";
	my $DatasourceDir   = $$outputFolderRef . $sumChartsFolder;
	my $datafile        = "ParetoChartDatasource.txt";
	my $pngfile         = "paretochartplot.png";
	unless ( -d "$DatasourceDir" ) { mkpath("$DatasourceDir"); }

	$$paretoDataFileRef    = "./$sumChartsFolder" . $datafile;
	$$paretoPNGFilePathRef = "./$sumChartsFolder" . $pngfile;

	my $m              = 0;
	my $paretoDataFile = $$outputFolderRef . $sumChartsFolder . $datafile;
	my $paretoPNGFile  = $$outputFolderRef . $sumChartsFolder . $pngfile;

	sysopen( FH, $paretoDataFile, O_WRONLY | O_TRUNC | O_CREAT ) || die $!;
	print FH "ErrorCode \t Frequency \n";

	foreach my $key (
		sort { $firstRunErrorCode{$b} <=> $firstRunErrorCode{$a} }
		keys %firstRunErrorCode )
	{
		next if ( $key =~ /^OK$/ );
		print FH "$key \t $firstRunErrorCode{$key} \n";
		$m += 1;
	}
	close(FH);

	## Generate Pareto chart
	$util->GenRChart_Pareto( $outputFolderRef, \$paretoDataFile,
		\$paretoPNGFile );

	# Generate test time box plot
	my @testtimebySTNAoA = grep { $_->[2] eq "OK" }
	  map [ $_->{"G_Computer_ID"},
		$_->{"G_Test_Duration"},
		$_->{"G_Error_1_Code"} ],
	  @$res_AoH;

	my %boxplotFilesAoA;
	$datafile = "Testtime_Boxplot_datafile.txt";
	$pngfile  = "Testtime_Boxplot_PNGFile.png";

	$$testtimeDatafileRef = "./$sumChartsFolder" . $datafile;
	$$testtimePNGFileRef = "./$sumChartsFolder" . $pngfile;

	my $testtimeDatafile = $$outputFolderRef . $sumChartsFolder . $datafile;
	my $testtimePNGfile  = $$outputFolderRef . $sumChartsFolder . $pngfile;
	$boxplotFilesAoA{"0"} = ( [ $testtimeDatafile, $testtimePNGfile, "Test Time" ] );

	sysopen( FH, $testtimeDatafile, O_WRONLY | O_TRUNC | O_CREAT ) || die $!;
	print FH "group	\t	value  \n";
	foreach (@testtimebySTNAoA) {
		$_->[0] =~ s/\s//g;
		print FH "$_->[0] \t $_->[1]  \n";
	}
	close(FH);

	$util->GenRChart_Boxplot( \%boxplotFilesAoA, $outputFolderRef, \0 );

	$util->logmsg( $op_textRef, "boxplot generated" );
}

#END {
#	# Close the database
#	$dbh->disconnect() if ( ref($dbh) );
#}

1;
__END__
# Below is stub documentation for your module. You'd better edit it!

=head1 NAME

GetSummaryDB - Perl extension for blah blah blah

=head1 SYNOPSIS

  use GetSummaryDB;
  blah blah blah

=head1 DESCRIPTION

Stub documentation for GetSummaryDB, created by h2xs. It looks like the
author of the extension was negligent enough to leave the stub
unedited.

Blah blah blah.

=head2 EXPORT

None by default.



=head1 SEE ALSO

Mention other useful documentation such as the documentation of
related modules or operating system documentation (such as man pages
in UNIX), or any relevant external documentation such as RFCs or
standards.

If you have a mailing list set up for your module, mention it here.

If you have a web site set up for your module, mention it here.

=head1 AUTHOR

A. U. Thor, E<lt>a.u.thor@a.galaxy.far.far.awayE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2011 by A. U. Thor

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.12.2 or,
at your option, any later version of Perl 5 you may have available.


=cut
